﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using DreamBuilder.UI.UserControls.DataAccess.Services;
using System.Data;
using System.Data.OracleClient;
using DreamBuilder.DictionaryAccess;


namespace DreamBuilder.UI.UserControls.DataAccess.Components
{
    public class OracleSPageListDAL : SPageListDAL
    {

        #region Oracle存储过程
        //create or replace procedure proc_qurey
        //(
        //        SelectStatement        in varchar2,   --
        //          FromStatement        in varchar2,   --表名
        //        WhereStatement         in varchar2,   --查询条件
        //        OrderByExpression      in varchar2,   --排序的列
        //        AscOrDesc       in varchar2,   --排序方式
        //          PageSize         in out Number, --每页显示记录条数
        //        PageIndex          in out Number, --当前页
        //        RecordCount          out Number,    --总记录条数
        //        v_cur              out SYS_REFCURSOR    --返回的结果集 
        //)
        //is
        //    v_sql VARCHAR2(1000) := '';      --sql语句
        //    v_startRecord Number(4);          --开始显示的记录条数
        //    v_endRecord Number(4);           --结束显示的记录条数 
        //    p_totalPages  Number(4);      --总页数
        //begin
        //      --记录中总记录条数
        //     v_sql:='select to_number(count(*)) from ' || FromStatement || ' where 1=1';
        //      if WhereStatement is not null or WhereStatement <> '' then
        //         v_sql:=v_sql || WhereStatement;
        //      end if;
        //    execute immediate v_sql into RecordCount;

        //    --验证页面记录大小
        //     if PageSize<0 then
        //        PageSize:=0;
        //     end if;

        //     --根据页大小计算总页数
        //     if mod(RecordCount,PageSize) =0 then
        //       p_totalPages := RecordCount /PageSize;
        //     else
        //       p_totalPages := RecordCount / PageSize + 1;
        //     end if;

        //    --验证页号
        //     if PageIndex<1 then
        //        PageIndex:=1;
        //     end if;

        //     if PageIndex > p_totalPages then
        //        PageIndex:=p_totalPages;
        //     end if;

        //    --实现分页查询

        //     v_startRecord:=(PageIndex-1)* PageSize+1;
        //     v_endRecord :=PageIndex* PageSize;

        //     v_sql := 'select * from (select A.*, rownum NumberIndex from ' || '(select ' || SelectStatement || ' from ' || FromStatement;

        //     if WhereStatement is not null or WhereStatement <> '' then
        //       v_sql := v_sql || ' where 1=1' || WhereStatement;
        //     end if;

        //     if OrderByExpression IS NOT NULL or OrderByExpression <> '' then
        //       v_sql := v_sql || ' order by ' || OrderByExpression || ' ' || AscOrDesc;
        //     end if;

        //       v_sql := v_sql || ')A where rownum <= ' || v_endRecord || ') b where NumberIndex >= ' || v_startRecord;

        //     DBMS_OUTPUT.put_line(v_sql);

        //     open v_cur for v_sql;
        //end proc_qurey;

        #endregion

        public override System.Data.DataTable SelectListPaged(string select, string from, string where, string orderBy, string ascOrDesc, int intPageIndex, int intPageSize, out int recordCount, Dictionary<string, string> dictionary)
        {
            OracleParameter v_cur = new OracleParameter("v_cur", OracleType.Cursor);
            v_cur.Direction = ParameterDirection.Output;

            OracleParameter p_recordCount = new OracleParameter("RecordCount", OracleType.Int32);
            p_recordCount.Direction = ParameterDirection.Output;

            OracleParameter p_pageIndex = new OracleParameter("PageIndex", OracleType.Int32);
            p_pageIndex.Value = intPageIndex;
            p_pageIndex.Direction = ParameterDirection.InputOutput;

            OracleParameter p_pageSize = new OracleParameter("PageSize", OracleType.Int32);
            p_pageSize.Value = intPageSize;
            p_pageSize.Direction = ParameterDirection.InputOutput;

            DbParameter[] parameters = new DbParameter[]
            {
                new  OracleParameter ("SelectStatement",select),
                new  OracleParameter ("FromStatement",from),
                new  OracleParameter ("WhereStatement",where),
                new  OracleParameter ("OrderByExpression",orderBy),
                new  OracleParameter ("AscOrDesc",ascOrDesc),
                p_pageIndex,
                p_pageSize,
                p_recordCount,
                v_cur

            };

            DataTable dtInfo = DBHelper.RunProcedureDataTable("proc_qurey", parameters);
            recordCount = Convert.ToInt32(p_recordCount.Value);
            foreach (string key in dictionary.Keys)
            {
                string aliasName = "";
                dictionary.TryGetValue(key, out aliasName);
                dtInfo.Columns[key].ColumnName = aliasName;
            }
            return dtInfo;

        }

        public override void GenerateListSql(CVoucherListItemInfo pListItemInfo, ref string selectSql, ref string fromSql, ref int columnIndex, Dictionary<string, string> tempDictionary)
        {
            string sTableCode = pListItemInfo.TableCode.Trim();
            string sFieldCode = pListItemInfo.FieldCode.Trim();
            FieldDicInfo pFieldDicInfo = DictionaryAccessFacade.GetFieldDicInfo(sTableCode, sFieldCode);

            //参照类型 这里如果是参照自身容易引发死循环问题 取两个值一个是ID，一个是Value
            if (pFieldDicInfo.FieldType == FIELDTYPE.TYPE_Reference)
            {
                //selectSql += sTableCode + "." + sFieldCode + " as COLUMN" + columnIndex + ",";
                //tempDictionary.Add("COLUMN" + columnIndex.ToString(), sTableCode + "." + sFieldCode);
                //columnIndex ++ ;
                //UCControlCommon.GetRefSelectAndFromSql(pFieldDicInfo, ref  fromSql, ref  selectSql, columnIndex);
                //tempDictionary.Add("COLUMN" + columnIndex.ToString(), sTableCode + "__" + sFieldCode);
                //columnIndex ++ ;
                selectSql += sTableCode + "." + sFieldCode + " as \"" + sTableCode + "." + sFieldCode + "\",";
                //获取显示值
                UCControlCommon.GetRefSelectAndFromSql(pFieldDicInfo, ref  fromSql, ref  selectSql);
            }
            //单选类型
            else if (pFieldDicInfo.FieldType == FIELDTYPE.TYPE_Bool)
            {
                //selectSql += "(case  " + sTableCode + "." + sFieldCode + " when 1 then 1 else 0 end) as COLUMN" + columnIndex + ",";
                //tempDictionary.Add("COLUMN" + columnIndex.ToString(), sTableCode + "." + sFieldCode);
                //columnIndex ++;
                //selectSql += "(case  " + sTableCode + "." + sFieldCode + " when 1 then '" + Common.CResManager.GetPublicString("String", "Yes") + "' else '" + Common.CResManager.GetPublicString("String", "No") + "' end) as COLUMN" + columnIndex + ",";
                //tempDictionary.Add("COLUMN" + columnIndex.ToString(), sTableCode + "__" + sFieldCode);
                //columnIndex ++;
                selectSql += "(case  " + sTableCode + "." + sFieldCode + " when 1 then '1' else '0' END) as \"" + sTableCode + "." + sFieldCode + "\",";
                selectSql += "(case  " + sTableCode + "." + sFieldCode + " when 1 then '" + Common.CResManager.GetPublicString("String", "Yes") + "' else '" + Common.CResManager.GetPublicString("String", "No") + "' end) as " + sTableCode + "__" + sFieldCode + ",";

            }
            else
            {
                //selectSql += sTableCode + "." + sFieldCode + " as COLUMN" + columnIndex + ",";
                //tempDictionary.Add("COLUMN" + columnIndex.ToString(), sTableCode + "__" + sFieldCode);
                //columnIndex ++;
                selectSql += sTableCode + "." + sFieldCode + " as " + sTableCode + "__" + sFieldCode + ",";
            }
        }

        public override string GetRestrictFKField(string tableCode, string refTableCode)
        {
            throw new NotImplementedException();
        }
    }
}
